# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import re

import xlrd
from xlutils.copy import copy


class HuaweiPipeline(object):
    def process_item(self, item, spider):
        # 保存到表格
        rb = xlrd.open_workbook('华为商城.xls')
        # 通过sheet_by_index()获取的sheet
        rs = rb.sheet_by_index(0)
        widths = self.set_width(rs)
        # print(widths)
        max_len = rs.nrows
        # 复制一个excel
        wb = copy(rb)
        # 通过获取到新的excel里面的sheet页
        ws = wb.get_sheet(0)
        # ws = wb.sheet_by_name(u"商品数据")
        cods = rs.col_values(0, start_rowx=0, end_rowx=None)
        ws.write(max_len, 0, item['coding'])
        ws.write(max_len, 1, item['classify_A'])
        ws.write(max_len, 2, item['classify_B'])
        ws.write(max_len, 3, item['title'])
        ws.write(max_len, 4, item['img'])
        ws.write(max_len, 5, item['href'])
        ws.write(max_len, 6, item['price'])
        ws.write(max_len, 7, item['comments'])
        ws.write(max_len, 8, item['content'])
        ws.write(max_len, 9, item['explain'])
        ws.write(max_len, 10, item['server_explain'])
        ws.write(max_len, 11, item['promotion'])
        for i in range(0, rs.ncols):
            ws.col(i).width = widths[i]*256 + 200
        wb.save('华为商城.xls')  # 保存新的excel，保存excel必须使用后缀名是.xls的，不是能是.xlsx的
        return item

    def set_width(self, sheet):
        col_width = []
        # 每列
        for i in range(0, sheet.ncols):
            columns = sheet.col_values(i, start_rowx=0, end_rowx=None)
            for co in columns:
                cos = str(co)
                j = 0
                for o in cos:
                    if re.match(r'[\u4E00-\u9FA5]', o):
                        # print('1')
                        j += 2
                    else:
                        # print("0")
                        j += 1
                # 每行
                if columns.index(co) == 0:
                    # 数组增加一个元素
                    col_width.append(j)
                else:
                    # 获得每列中的内容的最大宽度
                    if col_width[i] < j:
                        col_width[i] = j
        return col_width

        # 设置列宽
        #
        # for i in range(len(col_width)):
        #     # 根据列的数字返回字母
        #     # 当宽度大于100，宽度设置为100
        #     if col_width[i] > 100:
        #         print("大于100", col_width[i])
        #         sheet.col(0)[0].width = 100
        #     # 只有当宽度大于10，才设置列宽
        #     elif col_width[i] > 10:
        #         print("大于10", col_width[i])
        #         sheet.cell(0, 0).width = col_width[i] + 2
        #         print("witdh:", sheet.cell(0, 0).width)
        # return sheet